﻿using GBT.Tool.Core;
using Newtonsoft.Json.Linq;
using NLog;
using Persistence.Core;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

namespace QueryDataLogic.Core
{
    public class Evaluation
    {
        private static Logger logger = LogManager.GetCurrentClassLogger();
        ConfigManage cm = new ConfigManage();
        QueryData qd = new QueryData();
        DataPaging dp = new DataPaging();
        BasicInfo bi = new BasicInfo();

        /// <summary>
        /// 获取意见&建议
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="districtID"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        private string GetEvaluationIdea(int UserID, int districtID, string dbName)
        {
            string idea = "";
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select idea from EvaluationIdea  " +
                                "where districtID = @districtID and UserID = @UserID and [date] = CONVERT(varchar(100), GETDATE(), 23) "
                };

                comm.Parameters.AddWithValue("@districtID", districtID);
                comm.Parameters.AddWithValue("@UserID", UserID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    idea = (string)ja[0]["idea"];
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
            }
            return idea;
        }

        /// <summary>
        /// 获取食堂菜品质量评价单信息
        /// </summary>
        /// <returns></returns>
        public JObject GetEvaluationOrder(int userID, int districtID)
        {
            JObject joResult = new JObject();
            try
            {
                string dbName = "";
                string idea = "";

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select ectr.id,ectr.contentID,ec.number as contentNumber,ec.name as contentName,ectr.resultID, " +
                    "er.name as resultName,ectr.districtID,case when eu.ID is null then 0 else 1 end as isChecked " +
                    "from EvaluationContentToResult ectr " +
                    "inner join EvaluationContent ec on ectr.ContentID = ec.ID and ec.isEnable = 1 " +
                    "inner join EvaluationResult er on ectr.ResultID = er.ID " +
                    "left join EvaluationUser eu on eu.ECTRID = ectr.ID and eu.[date] = CONVERT(varchar(100), GETDATE(), 23) and eu.UserID = @userID " +
                    "where ectr.districtID = @districtID " +
                    "order by ectr.contentID "
                };
                comm.Parameters.AddWithValue("@userID", userID);
                comm.Parameters.AddWithValue("@districtID", districtID);

                dbName = bi.GetDBName(districtID);

                JArray ja = qd.GetData(comm, dbName);
                JObject jo = new JObject();
                int contentID = 0;
                JArray jaEvaluationOrder = new JArray();
                JArray jaOrder = new JArray();
                JArray jaItem = new JArray();
                JObject joItem = new JObject();
                if (ja.Count > 0)
                {
                    for (int index = 0; index < ja.Count; index++)
                    {
                        jo = (JObject)ja[index];
                        if (contentID != (int)jo["contentID"])
                        {
                            joItem = new JObject();
                            contentID = (int)jo["contentID"];
                            joItem["id"] = (int)jo["contentID"];
                            joItem["number"] = (string)jo["contentNumber"];
                            joItem["name"] = (string)jo["contentName"];
                            jaOrder.Add(joItem);
                        }
                    }
                    //logger.Info(jaOrder);
                    JObject joOrderItem = new JObject();
                    JObject joItems = new JObject();
                    contentID = 0;
                    for (int index = 0; index < jaOrder.Count; index++)
                    {
                        joOrderItem = (JObject)jaOrder[index];
                        contentID = (int)joOrderItem["id"];
                        joItems = new JObject();
                        jaItem = new JArray();
                        for (int jaIndex = 0; jaIndex < ja.Count; jaIndex++)
                        {
                            joItems = (JObject)ja[jaIndex];
                            if (contentID == (int)joItems["contentID"])
                            {
                                jaItem.Add(joItems);
                            }
                        }
                        joOrderItem["item"] = jaItem;
                        jaEvaluationOrder.Add(joOrderItem);
                    }
                    idea = GetEvaluationIdea(userID, districtID, dbName);
                    joResult.Add("idea", idea);
                    joResult.Add("data", jaEvaluationOrder);
                    joResult.Add("message", "评价单加载成功。");
                    joResult.Add("state", true);
                }
                else
                {
                    joResult.Add("data", new JArray());
                    joResult.Add("message", "评价单加载失败。");
                    joResult.Add("state", false);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult.Add("data", new JArray());
                joResult.Add("message", ex.Message + "\r\n" + ex.StackTrace);
                joResult.Add("state", false);
            }
            return joResult;
        }

        /// <summary>
        /// 获取意见&建议
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="districtID"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        private JArray GetEvaluationIdeaReport(int districtID, string date, string dbName)
        {
            JArray jaIdea = new JArray();
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select idea from EvaluationIdea  " +
                                "where districtID = @districtID and [date] = @date "
                };

                comm.Parameters.AddWithValue("@districtID", districtID);
                comm.Parameters.AddWithValue("@date", date);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    jaIdea = ja;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
            }
            return jaIdea;
        }

        /// <summary>
        /// 食堂菜品质量评价报告单
        /// </summary>
        /// <returns></returns>
        public JObject GetEvaluationReport(int districtID, string date)
        {
            JObject joResult = new JObject();
            try
            {
                string dbName = "";

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select ectr.id,ectr.contentID,content.contentNumber,content.contentName,ectr.resultID, " +
                    "er.number as resultNumber,er.name as resultName,ectr.districtID,isnull(total.resultTotal,0) as resultTotal, " +
                    "ISNULL(content1.contentTotal,0) as contentTotal from (select distinct ectr.contentID,ec.number as contentNumber, " +
                    "ec.name as contentName from EvaluationContentToResult ectr " +
                    "inner join EvaluationContent ec on ectr.ContentID = ec.ID " +
                    "inner join EvaluationResult er on ectr.ResultID = er.ID " +
                    "inner join EvaluationUser eu on eu.ECTRID = ectr.ID and eu.[date] = @date " +
                    "where ectr.districtID = 2) as content " +
                    "inner join EvaluationContentToResult ectr on ectr.contentID = content.contentID " +
                    "inner join EvaluationResult er on ectr.ResultID = er.ID " +
                    "left join (select ectr.contentID,ec.number as contentNumber,ec.name as contentName,ectr.resultID, " +
                    "er.name as resultName,ectr.districtID,COUNT(ectr.id) as resultTotal " +
                    "from EvaluationContentToResult ectr " +
                    "inner join EvaluationContent ec on ectr.ContentID = ec.ID " +
                    "inner join EvaluationResult er on ectr.ResultID = er.ID " +
                    "inner join EvaluationUser eu on eu.ECTRID = ectr.ID and eu.[date] = @date " +
                    "where ectr.districtID = @districtID " +
                    "group by ectr.contentID,ec.number,ec.name,ectr.resultID,er.name,ectr.districtID " +
                    ") as total on ectr.contentID = total.contentID and ectr.resultID = total.resultID " +
                    "inner join (select ectr.contentID,ectr.districtID,COUNT(ectr.id) as contentTotal " +
                    "from EvaluationContentToResult ectr " +
                    "left join EvaluationUser eu on eu.ECTRID = ectr.ID " +
                    "where eu.[date] = @date and eu.DistrictID = @districtID " +
                    "group by ectr.contentID,ectr.districtID " +
                    ") as content1 on content1.contentID = ectr.contentID " +
                    "order by ectr.contentID,ectr.resultID "
                };

                comm.Parameters.AddWithValue("@date", date);
                comm.Parameters.AddWithValue("@districtID", districtID);

                dbName = bi.GetDBName(districtID);

                JArray ja = qd.GetData(comm, dbName);
                JObject jo = new JObject();
                int contentID = 0;
                JArray jaEvaluationOrder = new JArray();
                JArray jaOrder = new JArray();
                JArray jaItem = new JArray();
                JObject joItem = new JObject();
                if (ja.Count > 0)
                {
                    for (int index = 0; index < ja.Count; index++)
                    {
                        jo = (JObject)ja[index];
                        if (contentID != (int)jo["contentID"])
                        {
                            joItem = new JObject();
                            contentID = (int)jo["contentID"];
                            joItem["id"] = (int)jo["contentID"];
                            joItem["number"] = (string)jo["contentNumber"];
                            joItem["name"] = (string)jo["contentName"];
                            joItem["contentTotal"] = (int)jo["contentTotal"];
                            jaOrder.Add(joItem);
                        }
                    }
                    //logger.Info(jaOrder);
                    JObject joOrderItem = new JObject();
                    JObject joItems = new JObject();
                    contentID = 0;
                    double resultRatio = 0d;
                    double contentTotal = 0d;
                    for (int index = 0; index < jaOrder.Count; index++)
                    {
                        joOrderItem = (JObject)jaOrder[index];
                        contentID = (int)joOrderItem["id"];
                        joItems = new JObject();
                        jaItem = new JArray();
                        for (int jaIndex = 0; jaIndex < ja.Count; jaIndex++)
                        {
                            joItems = (JObject)ja[jaIndex];
                            if (contentID == (int)joItems["contentID"])
                            {
                                contentTotal = (double)joItems["contentTotal"];
                                if (contentTotal > 0)
                                {
                                    resultRatio = ((double)joItems["resultTotal"]) / (contentTotal);
                                }
                                else
                                {
                                    resultRatio = 0d;
                                }
                                joItems["resultRatio"] = Math.Round(resultRatio, 2) * 100;
                                jaItem.Add(joItems);
                            }
                        }
                        joOrderItem["item"] = jaItem;
                        jaEvaluationOrder.Add(joOrderItem);
                    }

                    joResult.Add("idea", GetEvaluationIdeaReport(districtID, date, dbName));
                    joResult.Add("data", jaEvaluationOrder);
                    joResult.Add("message", "食堂菜品质量评价报告单加载成功。");
                    joResult.Add("state", true);
                }
                else
                {
                    joResult.Add("data", new JArray());
                    joResult.Add("message", "食堂菜品质量评价报告单加载失败。");
                    joResult.Add("state", false);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult.Add("data", new JArray());
                joResult.Add("message", ex.Message + "\r\n" + ex.StackTrace);
                joResult.Add("state", false);
            }
            return joResult;
        }
    }
}
